package sqldump

import (
	"bufio"
	"database/sql"
	"fmt"
	"io"
	"regexp"
	"strings"
	"time"
)

// ImportOptions contains options for SQL import operations
type ImportOptions struct {
	IgnoreErrors       bool   // Continue on SQL errors
	BatchSize         int    // Number of statements to execute in one transaction  
	SkipPragmas       bool   // Skip PRAGMA statements
	SkipComments      bool   // Skip comment lines
	MaxStatementSize  int    // Maximum size of a single SQL statement
	ReplaceExisting   bool   // Replace existing data instead of inserting
	DisableForeignKeys bool   // Temporarily disable foreign key constraints
	SkipDuplicates    bool   // Skip records that would cause constraint violations
	ClearBeforeImport bool   // Clear existing data before import
}

// DefaultImportOptions returns default options for SQL import
func DefaultImportOptions() *ImportOptions {
	return &ImportOptions{
		IgnoreErrors:      false,
		BatchSize:         100,
		SkipPragmas:       false,
		SkipComments:      true,
		MaxStatementSize:  1024 * 1024, // 1MB
		ReplaceExisting:   false,
		DisableForeignKeys: true,
		SkipDuplicates:    true,
		ClearBeforeImport: false,
	}
}

// ImportResult contains the result of an import operation
type ImportResult struct {
	StatementsExecuted int      // Number of statements executed
	StatementsSkipped  int      // Number of statements skipped
	RecordsInserted   int      // Number of records inserted
	RecordsUpdated    int      // Number of records updated
	RecordsSkipped    int      // Number of records skipped due to duplicates
	TablesCleared     []string // Tables that were cleared before import
	Errors            []string // List of errors encountered
	Warnings          []string // List of warnings
	Success           bool     // Overall success status
	Duration          string   // Import duration
}

// SQLImporter provides methods to import SQL dump into SQLite database
type SQLImporter struct {
	db      *sql.DB
	options *ImportOptions
}

// NewSQLImporter creates a new SQL importer instance
func NewSQLImporter(db *sql.DB, options *ImportOptions) *SQLImporter {
	if options == nil {
		options = DefaultImportOptions()
	}
	return &SQLImporter{
		db:      db,
		options: options,
	}
}

// ImportFromReader reads and executes SQL statements from the provided reader
func (i *SQLImporter) ImportFromReader(r io.Reader) (*ImportResult, error) {
	result := &ImportResult{
		Success: true,
	}

	// Record start time
	startTime := time.Now()
	defer func() {
		result.Duration = time.Since(startTime).String()
	}()

	// Parse SQL statements from reader
	statements, err := i.parseSQL(r)
	if err != nil {
		return nil, fmt.Errorf("failed to parse SQL: %w", err)
	}

	// Prepare database for import
	if err := i.prepareForImport(result); err != nil {
		return nil, fmt.Errorf("failed to prepare for import: %w", err)
	}

	// Execute statements
	finalResult, err := i.executeStatements(statements, result)

	// Restore database settings
	if restoreErr := i.restoreAfterImport(); restoreErr != nil {
		if err == nil {
			err = fmt.Errorf("failed to restore after import: %w", restoreErr)
		} else {
			finalResult.Warnings = append(finalResult.Warnings, fmt.Sprintf("Failed to restore settings: %v", restoreErr))
		}
	}

	return finalResult, err
}

// parseSQL parses SQL statements from reader
func (i *SQLImporter) parseSQL(r io.Reader) ([]string, error) {
	scanner := bufio.NewScanner(r)
	
	// Increase buffer size for large statements
	buf := make([]byte, 0, 64*1024)
	scanner.Buffer(buf, i.options.MaxStatementSize)

	var statements []string
	var currentStatement strings.Builder
	inMultilineComment := false

	for scanner.Scan() {
		line := strings.TrimSpace(scanner.Text())

		// Skip empty lines
		if line == "" {
			continue
		}

		// Handle multiline comments
		if strings.HasPrefix(line, "/*") {
			inMultilineComment = true
			if strings.HasSuffix(line, "*/") {
				inMultilineComment = false
			}
			continue
		}
		if inMultilineComment {
			if strings.HasSuffix(line, "*/") {
				inMultilineComment = false
			}
			continue
		}

		// Skip single line comments
		if i.options.SkipComments && strings.HasPrefix(line, "--") {
			continue
		}

		// Skip pragma statements if requested
		if i.options.SkipPragmas && strings.HasPrefix(strings.ToUpper(line), "PRAGMA") {
			continue
		}

		// Add line to current statement
		if currentStatement.Len() > 0 {
			currentStatement.WriteString(" ")
		}
		currentStatement.WriteString(line)

		// Check if statement is complete (ends with semicolon)
		if strings.HasSuffix(line, ";") {
			stmt := strings.TrimSpace(currentStatement.String())
			if stmt != "" && stmt != ";" {
				statements = append(statements, stmt)
			}
			currentStatement.Reset()
		}
	}

	// Handle incomplete statement
	if currentStatement.Len() > 0 {
		stmt := strings.TrimSpace(currentStatement.String())
		if stmt != "" {
			statements = append(statements, stmt)
		}
	}

	return statements, scanner.Err()
}

// executeStatements executes parsed SQL statements
func (i *SQLImporter) executeStatements(statements []string, result *ImportResult) (*ImportResult, error) {
	batchSize := i.options.BatchSize
	if batchSize <= 0 {
		batchSize = 100
	}

	for idx := 0; idx < len(statements); idx += batchSize {
		end := idx + batchSize
		if end > len(statements) {
			end = len(statements)
		}

		// Execute batch in transaction
		if err := i.executeBatch(statements[idx:end], result); err != nil {
			if !i.options.IgnoreErrors {
				result.Success = false
				return result, err
			}
			// Log error but continue
			result.Errors = append(result.Errors, fmt.Sprintf("Batch error: %v", err))
		}
	}

	return result, nil
}

// executeBatch executes a batch of statements in a transaction
func (i *SQLImporter) executeBatch(statements []string, result *ImportResult) error {
	tx, err := i.db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback()

	for _, stmt := range statements {
		stmt = strings.TrimSpace(stmt)
		if stmt == "" {
			continue
		}

		// Execute statement with constraint handling
		if err := i.executeStatementWithRetry(tx, stmt, result); err != nil {
			if i.options.IgnoreErrors {
				result.Errors = append(result.Errors, fmt.Sprintf("Statement error: %v (SQL: %.100s...)", err, stmt))
				result.StatementsSkipped++
				continue
			}
			return fmt.Errorf("failed to execute statement: %w (SQL: %.100s...)", err, stmt)
		}

		result.StatementsExecuted++
	}

	// Commit transaction
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	return nil
}

// executeStatementWithRetry executes a statement with retry logic for constraint handling
func (i *SQLImporter) executeStatementWithRetry(tx *sql.Tx, stmt string, result *ImportResult) error {
	// First try: execute as-is
	err := i.executeStatement(tx, stmt)
	if err == nil {
		// Check if it's an INSERT statement for counting
		if strings.HasPrefix(strings.ToUpper(strings.TrimSpace(stmt)), "INSERT") {
			result.RecordsInserted++
		}
		return nil
	}

	// If it's not a constraint error, return the error
	if !i.isConstraintError(err) {
		return err
	}

	// Handle constraint errors based on options
	if strings.HasPrefix(strings.ToUpper(strings.TrimSpace(stmt)), "INSERT") {
		if i.options.SkipDuplicates {
			// Try INSERT OR IGNORE
			ignoreStmt := i.convertInsertToInsertIgnore(stmt)
			if err := i.executeStatement(tx, ignoreStmt); err == nil {
				result.RecordsSkipped++
				result.Warnings = append(result.Warnings, fmt.Sprintf("Skipped duplicate record: %.100s...", stmt))
				return nil
			}
		}

		if i.options.ReplaceExisting {
			// Try REPLACE
			replaceStmt := i.convertInsertToReplace(stmt)
			if err := i.executeStatement(tx, replaceStmt); err == nil {
				result.RecordsUpdated++
				result.Warnings = append(result.Warnings, fmt.Sprintf("Replaced existing record: %.100s...", stmt))
				return nil
			}
		}
	}

	// If all retries failed, return the original error
	return err
}

// executeStatement executes a single SQL statement
func (i *SQLImporter) executeStatement(tx *sql.Tx, stmt string) error {
	// Handle special cases
	upperStmt := strings.ToUpper(strings.TrimSpace(stmt))

	// Skip certain statements that might cause issues
	if strings.HasPrefix(upperStmt, "PRAGMA FOREIGN_KEYS") ||
		strings.HasPrefix(upperStmt, "BEGIN TRANSACTION") ||
		strings.HasPrefix(upperStmt, "COMMIT") ||
		strings.HasPrefix(upperStmt, "ROLLBACK") {
		return nil
	}

	// Execute the statement
	_, err := tx.Exec(stmt)
	return err
}

// ValidateSQL validates SQL statements without executing them
func (i *SQLImporter) ValidateSQL(r io.Reader) ([]string, error) {
	statements, err := i.parseSQL(r)
	if err != nil {
		return nil, err
	}

	var errors []string

	for idx, stmt := range statements {
		// Basic syntax validation by preparing the statement
		if prepStmt, err := i.db.Prepare(stmt); err != nil {
			errors = append(errors, fmt.Sprintf("Statement %d: %v (SQL: %.100s...)", idx+1, err, stmt))
		} else {
			prepStmt.Close()
		}
	}

	return errors, nil
}

// prepareForImport prepares the database for import operation
func (i *SQLImporter) prepareForImport(result *ImportResult) error {
	// Disable foreign key constraints if requested
	if i.options.DisableForeignKeys {
		if _, err := i.db.Exec("PRAGMA foreign_keys=OFF"); err != nil {
			return fmt.Errorf("failed to disable foreign keys: %w", err)
		}
	}

	// Clear tables if requested
	if i.options.ClearBeforeImport {
		if err := i.clearExistingData(result); err != nil {
			return fmt.Errorf("failed to clear existing data: %w", err)
		}
	}

	return nil
}

// restoreAfterImport restores database settings after import
func (i *SQLImporter) restoreAfterImport() error {
	// Re-enable foreign key constraints
	if i.options.DisableForeignKeys {
		if _, err := i.db.Exec("PRAGMA foreign_keys=ON"); err != nil {
			return fmt.Errorf("failed to re-enable foreign keys: %w", err)
		}
	}

	return nil
}

// clearExistingData clears existing data from tables in correct order
func (i *SQLImporter) clearExistingData(result *ImportResult) error {
	// Get table list ordered by dependency (reverse of creation order)
	tables := []string{
		"label_items",           // Junction table
		"auth_roles",            // References auth_tokens
		"auth_tokens",           // References users
		"attachments",           // References items (self-reference and items)
		"item_fields",           // References items
		"maintenance_entries",   // References items
		"items",                 // References groups, locations, self
		"notifiers",             // References groups, users
		"labels",                // References groups
		"locations",             // References groups, self
		"group_invitation_tokens", // References groups
		"users",                 // References groups
		"groups",                // No foreign key dependencies
	}

	// Clear tables in dependency order
	for _, table := range tables {
		if err := i.clearTable(table); err != nil {
			result.Warnings = append(result.Warnings, fmt.Sprintf("Failed to clear table %s: %v", table, err))
		} else {
			result.TablesCleared = append(result.TablesCleared, table)
		}
	}

	return nil
}

// clearTable clears a specific table
func (i *SQLImporter) clearTable(table string) error {
	// Check if table exists
	var count int
	err := i.db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?", table).Scan(&count)
	if err != nil {
		return err
	}
	if count == 0 {
		return nil // Table doesn't exist, skip
	}

	// Clear the table
	_, err = i.db.Exec(fmt.Sprintf("DELETE FROM `%s`", table))
	return err
}

// isConstraintError checks if an error is a constraint violation
func (i *SQLImporter) isConstraintError(err error) bool {
	if err == nil {
		return false
	}
	errorStr := strings.ToLower(err.Error())
	return strings.Contains(errorStr, "constraint") ||
		strings.Contains(errorStr, "unique") ||
		strings.Contains(errorStr, "foreign key") ||
		strings.Contains(errorStr, "primary key")
}

// extractTableFromInsert extracts table name from INSERT statement
func (i *SQLImporter) extractTableFromInsert(stmt string) string {
	// Pattern to match INSERT INTO table_name
	pattern := `(?i)INSERT\s+INTO\s+` + "`?([^\\s` ]+)`?"
	re := regexp.MustCompile(pattern)
	matches := re.FindStringSubmatch(stmt)
	if len(matches) > 1 {
		return strings.Trim(matches[1], "`")
	}
	return ""
}

// convertInsertToReplace converts INSERT statement to REPLACE statement
func (i *SQLImporter) convertInsertToReplace(stmt string) string {
	return regexp.MustCompile(`(?i)^INSERT`).ReplaceAllString(stmt, "REPLACE")
}

// convertInsertToInsertIgnore converts INSERT statement to INSERT OR IGNORE statement
func (i *SQLImporter) convertInsertToInsertIgnore(stmt string) string {
	return regexp.MustCompile(`(?i)^INSERT`).ReplaceAllString(stmt, "INSERT OR IGNORE")
}

// ClearDatabase clears all data from the database in proper dependency order
func (i *SQLImporter) ClearDatabase() (*ClearResult, error) {
	result := &ClearResult{
		Success: true,
	}

	// Record start time
	startTime := time.Now()
	defer func() {
		result.Duration = time.Since(startTime).String()
	}()

	// Get all tables to clear
	tables, err := i.getAllTables()
	if err != nil {
		return nil, fmt.Errorf("failed to get tables: %w", err)
	}

	// Clear existing data
	if err := i.clearAllTables(result, tables); err != nil {
		return nil, fmt.Errorf("failed to clear tables: %w", err)
	}

	return result, nil
}

// ClearResult contains the result of a clear operation
type ClearResult struct {
	TablesCleared []string // Tables that were cleared
	RecordsDeleted int     // Total number of records deleted
	Errors        []string // List of errors encountered
	Warnings      []string // List of warnings
	Success       bool     // Overall success status
	Duration      string   // Clear operation duration
}

// getAllTables retrieves all table names from the database
func (i *SQLImporter) getAllTables() ([]string, error) {
	query := `SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name`
	
	rows, err := i.db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var tableName string
		if err := rows.Scan(&tableName); err != nil {
			return nil, err
		}
		tables = append(tables, tableName)
	}

	return tables, rows.Err()
}

// clearAllTables clears all tables in proper dependency order
func (i *SQLImporter) clearAllTables(result *ClearResult, allTables []string) error {
	// Define table clearing order based on foreign key dependencies
	// Clear child tables first, then parent tables
	clearOrder := []string{
		"label_items",           // Junction table
		"auth_roles",            // References auth_tokens
		"auth_tokens",           // References users
		"attachments",           // References items (self-reference and items)
		"item_fields",           // References items
		"maintenance_entries",   // References items
		"items",                 // References groups, locations, self
		"notifiers",             // References groups, users
		"labels",                // References groups
		"locations",             // References groups, self
		"group_invitation_tokens", // References groups
		"users",                 // References groups
		"groups",                // No foreign key dependencies
	}

	// Add any additional tables that might exist but aren't in our predefined order
	existingTables := make(map[string]bool)
	for _, table := range allTables {
		existingTables[table] = true
	}

	// Clear tables in dependency order
	for _, table := range clearOrder {
		if existingTables[table] {
			if err := i.clearSingleTable(table, result); err != nil {
				result.Warnings = append(result.Warnings, fmt.Sprintf("Failed to clear table %s: %v", table, err))
			} else {
				result.TablesCleared = append(result.TablesCleared, table)
			}
			delete(existingTables, table)
		}
	}

	// Clear any remaining tables that weren't in our predefined order
	for table := range existingTables {
		if err := i.clearSingleTable(table, result); err != nil {
			result.Warnings = append(result.Warnings, fmt.Sprintf("Failed to clear table %s: %v", table, err))
		} else {
			result.TablesCleared = append(result.TablesCleared, table)
		}
	}

	return nil
}

// clearSingleTable clears a specific table and counts deleted records
func (i *SQLImporter) clearSingleTable(table string, result *ClearResult) error {
	// Count records before deletion
	var recordCount int
	err := i.db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM `%s`", table)).Scan(&recordCount)
	if err != nil {
		return fmt.Errorf("failed to count records in table %s: %w", table, err)
	}

	// Skip if table is already empty
	if recordCount == 0 {
		return nil
	}

	// Clear the table
	_, err = i.db.Exec(fmt.Sprintf("DELETE FROM `%s`", table))
	if err != nil {
		return fmt.Errorf("failed to delete records from table %s: %w", table, err)
	}

	// Update total deleted records count
	result.RecordsDeleted += recordCount

	return nil
}